﻿using System.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{   
    public class SupplierGateway:DbGateway
    {
        public string SaveSupplierGateway(Supplier aSupplier)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosSupplier VALUES(@supplierName,@supplierAddress,@supplierPerson,@supplierCell,@supplierFax,@supplierEmail,@supplierType,@supplierMode)");
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = query;
                CommandObj.Parameters.Add("@supplierName", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierName"].Value = aSupplier.SupplierName;
                CommandObj.Parameters.Add("@supplierAddress", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierAddress"].Value = aSupplier.SupplierAddress;
                CommandObj.Parameters.Add("@supplierPerson", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierPerson"].Value = aSupplier.SupplierPerson;
                CommandObj.Parameters.Add("@supplierCell", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierCell"].Value = aSupplier.SupplierCell;
                CommandObj.Parameters.Add("@supplierFax", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierFax"].Value = aSupplier.SupplierFax;
                CommandObj.Parameters.Add("@supplierEmail", SqlDbType.VarChar);
                CommandObj.Parameters["@supplierEmail"].Value = aSupplier.SupplierEmail;
                CommandObj.Parameters.Add("@supplierType", SqlDbType.Int);
                CommandObj.Parameters["@supplierType"].Value = aSupplier.SupplierType;
                CommandObj.Parameters.Add("@supplierMode", SqlDbType.Int);
                CommandObj.Parameters["@supplierMode"].Value = aSupplier.SupplierMode;

                CommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Supplier couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "Save Successful";    
        }

        public List<Supplier> GetAllSupplier()
        {
            List<Supplier> suppliers = new List<Supplier>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosSupplier");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Supplier aSupplier = new Supplier();
                    aSupplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    aSupplier.SupplierName = reader["SupplierName"].ToString();
                    aSupplier.SupplierPerson = reader["SupplierPerson"].ToString();
                    aSupplier.SupplierCell = reader["SupplierCell"].ToString();
                    suppliers.Add(aSupplier);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Supplier loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return suppliers;
        }

        public int GetAllSupplierId(int supplierId)
        {
            try
            {
                ConnectionObj.Open();
                string query = "Select supplierId from PharSupplier where supplierId=" + supplierId;
                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();       
            }
            catch (Exception)
            {             
                throw;
            }           
            return supplierId;
        }

        public Supplier FindSupplier(string supplierName)
        {
            try
            {
                string search = String.Format("Select * FROM PosSupplier WHERE SupplierName=@SupplierName");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@SupplierName", SqlDbType.VarChar);
                CommandObj.Parameters["@SupplierName"].Value = supplierName;
                CommandObj.CommandText = search;
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                Supplier supplier = new Supplier();
                if (sqlDataReader != null)
                {
                    while (sqlDataReader.Read())
                    {
                        supplier.SupplierId = Convert.ToInt32(sqlDataReader["supplierId"]);
                    }
                }
                return supplier;
            }

            catch (Exception exception)
            {
                throw new Exception("Could not Found Supplier", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        //public string NextID()
        //{
        //    ConnectionObj.Close();
        //    var table = new DataTable();

        //    string sql = "Select max(PurchaseNo) From PharPurchaseDtls";
        //    CommandObj.Parameters.Clear();
        //    CommandObj.CommandText = sql;
        //    ConnectionObj.Open();
        //    SqlDataAdapter ad = new SqlDataAdapter(CommandObj);
        //    ad.Fill(table);
        //    string id = table.Rows[0][0].ToString();
        //    if (id == "")
        //    {
        //        string dd = "000001";
        //        return dd;
        //    }
        //    else
        //    {
        //        string temp;
        //        int Nonzero;
        //        int length;
        //        temp = table.Rows[0][0].ToString().Trim();
        //        Nonzero = Convert.ToInt32(table.Rows[0][0].ToString().Trim());
        //        int ssss = Nonzero + 1;
        //        length = temp.Length - ssss.ToString().Length;
        //        string mo = (temp.Substring(0, length) + ssss.ToString());
        //        return mo;
        //    }
        //}

        public string GetAllSupplierName()
        {
            string suggestionList = "";
            try
            {
                string queryString = string.Format("SELECT  supplierName FROM  PosSupplier");

                CommandObj.Connection = ConnectionObj;
                CommandObj.CommandText = queryString;
                ConnectionObj.Open();
                SqlDataReader reader = CommandObj.ExecuteReader();

                while (reader.Read())
                {
                    if (string.IsNullOrEmpty(suggestionList))
                    {
                        suggestionList += "\"" + reader["supplierName"].ToString() + "\"";
                    }
                    else
                    {
                        suggestionList += ", \"" + reader["supplierName"].ToString() + "\"";
                    }
                }
            }

            catch (Exception exception)
            {
                throw new Exception("Couldn't Loaded Data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }

            return suggestionList;
        }

        public string DeleteSupplierById(int supplierId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosSupplier WHERE supplierId='{0}'", supplierId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "supplier Code does not found";
            }
        }

        public List<Supplier> GetAllSupplierByCodeOrName(int i, string supplierId)
        {
            List<Supplier> suppliers = new List<Supplier>();
            string select = "";

            try
            {
                ConnectionObj.Open();
                if (i == 0)
                {

                    select = String.Format("SELECT * FROM PosSupplier WHERE supplierId ='{0}'", Convert.ToInt32(supplierId));
                }
                if (i == 1)
                {
                    supplierId = supplierId + "%";
                    select = String.Format("SELECT * FROM PosSupplier WHERE supplierName LIKE '" + supplierId + "'");
                }

                CommandObj.CommandText = select;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = Convert.ToInt32(reader[0]);
                    supplier.SupplierName = reader[1].ToString();
                    suppliers.Add(supplier);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return suppliers;
        }



    }
}